#region Imported Namespaces
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.IO;

using PHSRAG.Insight21.InsightUtilities;
using BO = PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using SearchCriteria = PHSRAG.Insight21.SearchContext.SearchCriteria;
using ModuleKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;
#endregion

namespace PHSRAG.Insight21.Agreements
{
    /// <summary>
    /// Stimulus Reporting Class retrieves the ARRA reporting data and generates XML reports.
    /// </summary>
    public class StimulusFunding   //implementing te business object
    {
        //        #region Constants
        //        private static string StimulusReportingKey = ModuleKey.MakeKey("StimulusReporting", ModuleKey.Agreement);
        //        #endregion
        public XmlDocument doc = null;  // put this file inside Arra

        private bool IsDate(string sdate)
        {
            try
            {
                DateTime.Parse(sdate);
                return true;
            }
            catch
            {
            }
            return false;
        }

        /// <summary>
        /// This method fetches the Current Report Period based on Today's Date.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetCurrentReportPeriod(string connectionString)
        {
            try
            {
                return DataAccess.GetDataTable(connectionString, "usp_ArraGetCurrentReportPeriod", null); // avoid usp_
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Current Report Period ,Error occurred while executing the StimulusFunding.GetCurrentReportPeriod()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method fetches all the Report Periods from the table to populate the drop down in Search Box.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetAllReportPeriod(string connectionString)
        {
            try
            {
                return DataAccess.GetDataTable(connectionString, "usp_ArraGetAllReportPeriod", null);
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Report Periods, Error occurred while executing the StimulusFunding.GetAllReportPeriod()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method fetches the Proposal Data that will be bound to the Proposal grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetProposalData(string connectionString, string PSFundNo, string AwardNo, string PILastName, string PIFirstName, string SponsorName, string ProposalNo, string DAFirstName, string DALastName, string DocumentID, int ReportPeriodID, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder, int loggedInUserId)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.usp_ArraGetProposalData", DataAccess.BuildSqlParameterArray(
                        "@ReportPeriodId", SqlDbType.Int, ReportPeriodID,
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PSFundNo", SqlDbType.VarChar, PSFundNo,
                        "@SponsorAwardNo", SqlDbType.VarChar, AwardNo,
                        "@PILastName", SqlDbType.VarChar, PILastName,
                        "@PIFirstName", SqlDbType.VarChar, PIFirstName,
                        "@Sponsor", SqlDbType.VarChar, SponsorName,
                        "@ProposalNo", SqlDbType.VarChar, ProposalNo,
                        "@DAFirstName", SqlDbType.VarChar, DAFirstName,
                        "@DALastName", SqlDbType.VarChar, DALastName,
                        "@DocumentID", SqlDbType.VarChar, DocumentID,
                        "@OrderBy", SqlDbType.VarChar, SortOrder,
                        "@PageSize", SqlDbType.Int, PageSize,
                        "@PageStartIndex", SqlDbType.Int, StartPageIndex,
                        "@PageEndIndex", SqlDbType.Int, EndingPageIndex));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Stimulus Funding ,Error occurred while executing the StimulusFunding.GetProposalData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method fetches the Proposal Data that will be bound to the Proposal grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetProposalReportData(string connectionString, string strReportPeriod)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.usp_ArraGetProposalReportData", DataAccess.BuildSqlParameterArray(
                        "@pReportPeriodId", SqlDbType.VarChar, strReportPeriod));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Stimulus Funding ,Error occurred while executing the StimulusFunding.GetProposalData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method fetches the Sub Recipient Data that will be bound to the Sub Recipient grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetSubRecipientData(string connectionString, string PSFundNo, string AwardNo, string PILastName, string PIFirstName, string SponsorName, string ProposalNo, string DAFirstName, string DALastName, string DocumentID, int ReportPeriodID, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder, int loggedInUserId)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.usp_ArraGetSubRecipientData", DataAccess.BuildSqlParameterArray(
                        "@ReportPeriodId", SqlDbType.Int, ReportPeriodID,
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PSFundNo", SqlDbType.VarChar, PSFundNo,
                        "@SponsorAwardNo", SqlDbType.VarChar, AwardNo,
                        "@PILastName", SqlDbType.VarChar, PILastName,
                        "@PIFirstName", SqlDbType.VarChar, PIFirstName,
                        "@Sponsor", SqlDbType.VarChar, SponsorName,
                        "@ProposalNo", SqlDbType.VarChar, ProposalNo,
                        "@DAFirstName", SqlDbType.VarChar, DAFirstName,
                        "@DALastName", SqlDbType.VarChar, DALastName,
                        "@DocumentID", SqlDbType.VarChar, DocumentID,
                        "@OrderBy", SqlDbType.VarChar, SortOrder,
                        "@PageSize", SqlDbType.Int, PageSize,
                        "@PageStartIndex", SqlDbType.Int, StartPageIndex,
                        "@PageEndIndex", SqlDbType.Int, EndingPageIndex));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Stimulus Funding ,Error occurred while executing the StimulusFunding.GetSubRecipientData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method fetches the Sub Recipient Data that will be bound to the Sub Recipient grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetSubRecipientReportData(string connectionString, string strReportPeriod)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.usp_ArraGetSubRecipientReportData", DataAccess.BuildSqlParameterArray(
                        "@pReportPeriodId", SqlDbType.VarChar, strReportPeriod));
                         
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Stimulus Funding ,Error occurred while executing the StimulusFunding.GetSubRecipientData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }


        /// <summary>
        /// This method fetches the Vendor Data that will be bound to the Vendor grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetVendorData(string connectionString, string PSFundNo, string AwardNo, string PILastName, string PIFirstName, string SponsorName, string ProposalNo, string DAFirstName, string DALastName, string DocumentID, int ReportPeriodID, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder, int loggedInUserId)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.usp_ArraGetVendorData", DataAccess.BuildSqlParameterArray(
                        "@ReportPeriodId", SqlDbType.Int, ReportPeriodID,
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PSFundNo", SqlDbType.VarChar, PSFundNo,
                        "@SponsorAwardNo", SqlDbType.VarChar, AwardNo,
                        "@PILastName", SqlDbType.VarChar, PILastName,
                        "@PIFirstName", SqlDbType.VarChar, PIFirstName,
                        "@Sponsor", SqlDbType.VarChar, SponsorName,
                        "@ProposalNo", SqlDbType.VarChar, ProposalNo,
                        "@DAFirstName", SqlDbType.VarChar, DAFirstName,
                        "@DALastName", SqlDbType.VarChar, DALastName,
                        "@DocumentID", SqlDbType.VarChar, DocumentID,
                        "@OrderBy", SqlDbType.VarChar, SortOrder,
                        "@PageSize", SqlDbType.Int, PageSize,
                        "@PageStartIndex", SqlDbType.Int, StartPageIndex,
                        "@PageEndIndex", SqlDbType.Int, EndingPageIndex));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Stimulus Funding ,Error occurred while executing the StimulusFunding.GetVendorData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method fetches the Vendor Data that will be bound to the Vendor grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetVendorReportData(string connectionString, string strReportPeriod)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.usp_ArraGetVendorReportData", DataAccess.BuildSqlParameterArray(
                        "@pReportPeriodId", SqlDbType.VarChar, strReportPeriod));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Stimulus Funding ,Error occurred while executing the StimulusFunding.GetVendorData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// Method to freeze the Report IsFinal Flag for the given reporting period.
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public bool FreezeReportData(string connectionString, string strReportPeriod) // use dbo. before any SP
        {
            try
            {
                DataAccess.ExecuteNonQuery(connectionString, "usp_ArraUpdateReportIsFinalFlag", DataAccess.BuildSqlParameterArray(
                        "@pReportPeriodId", SqlDbType.VarChar, strReportPeriod));
                return true;
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to update the Report IsFinal Flag, Error occurred while executing the StimulusFunding.FreezeReportData()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// Method to add a Xml node. 
        /// </summary>
        /// <param name="doc">Xml Document</param>
        /// <param name="root">Root Node</param>
        /// <param name="name">Element Name</param>
        /// <param name="val">Value</param>
        /// <returns></returns>
        private XmlNode addNode(XmlDocument doc, XmlNode root, string name, string val)
        {
            XmlNode node = doc.CreateNode(XmlNodeType.Element, name, null);
            node.InnerText = val;
            root.AppendChild(node);
            return node;
        }

        /// <summary>
        /// Method to add an Xml attribute to a node.
        /// </summary>
        /// <param name="doc">Xml Document</param>
        /// <param name="root">Root Node</param>
        /// <param name="name">Element Name</param>
        /// <param name="val">Value</param>
        private void addAttribute(XmlDocument doc, XmlNode root, string name, string val)
        {
            XmlAttribute attribute = doc.CreateAttribute(name);
            attribute.InnerText = val;
            root.Attributes.Append(attribute);
        }

        /// <summary>
        /// Method to save the XML string to a XML file.
        /// </summary>
        /// <param name="FileName">XML file name</param>
        /// <param name="sXml">XML string</param>
        private void SaveXML(string FileName, string sXml)
        {
            StreamWriter objStreamWriter = File.CreateText(FileName);
            objStreamWriter.Write(sXml);
            objStreamWriter.Close();
        }
    }
}    
